import os
import pandas as pd
import re
import xlwings as xw
files = os.listdir('./')
path = None
for i in files:
    if 'D84-BATTERY-AND-VHB-INSTALL-Versions' in i :
        path = i

col = ['Station ID', 'Dynamic_Rolling_Time']

df = pd.read_csv( path , header=1, skiprows=[2, 3, 4, 5, 6],
    usecols=col)


df['Station ID'] = df['Station ID'].apply(lambda x : (re.findall(r'[A-Z0-9]+-[A-Z0-9]+-[A-Z0-9_]+' , x ))[0] )
df = df.drop_duplicates(subset=['Station ID'], keep='last')
print(df)
df[['Floor','Line' ,'s']] = df['Station ID'].str.split('_',expand  = True)
df['Floor'] = df['Floor'].apply(lambda x :  x.split('-')[-1] +  '-' +  x.split('-')[0] + '-' + x.split('-')[1])
print(df.head(10))

df = df.reindex(['Floor', 'Line', 'Dynamic_Rolling_Time' ],axis =1)
df = df.rename(columns={"Dynamic_Rolling_Time":'time'})

df = df.sort_values(by=['Floor', 'Line'])
df['Floor'] = df['Floor'].apply(lambda x:  x.split('-')[1] + '-' + x.split('-')[-1] + '-' + x.split('-')[0])
df['Line'] = df['Line'].astype(int)
df.to_excel('sort.xlsx', index=False)

with  xw.App (visible=False)as app:
    wb = app.books.open('sort.xlsx')
    sht = wb.sheets[0]
    sht.autofit()
    for n, v in enumerate(sht.range('C2').expand('down').value):
        print(n,v)
        if v < 11:
            station = f'c{n+2}'
            sht.range(station).color = (135, 206, 250)
    wb.save()


